/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.util.ArrayList;
import java.sql.CallableStatement;

import Model.Model_Question;
import Util.DataUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

;

/**
 *
 * @author TuanDat
 */
public class DAO_Question {

    /**
     *
     * @author NUOINV get all question Category
     */
    public Model_Question get_TopQuestion() {
        Model_Question modquest = new Model_Question();
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_GetTopQuestion}");
            ResultSet rs = csmt.executeQuery();
            while (rs.next()) {
                modquest.setId(rs.getString("ID"));
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return modquest;
    }

     public Model_Question get_QuestionByID(String id) {
        Model_Question modQuestion = new Model_Question();

        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_GetQuestionByID(?)}");
            csmt.setString("ID", id);
            ResultSet rs = csmt.executeQuery();
            while (rs.next()) {
                modQuestion.setId(rs.getString("ID"));
                modQuestion.setCategory_ID(rs.getString("Category_ID"));
                modQuestion.setContent(rs.getString("Content"));
                modQuestion.setStatus(rs.getString("Status"));
                modQuestion.setLast_update(rs.getString("Last_Update").toString());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return modQuestion;
    }
    /**
     *
     * @author NUOINV get all question Category
     */
    public ArrayList<Model_Question> get_AllQuestion() {
        ArrayList<Model_Question> listQuestion = new ArrayList<Model_Question>();

        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_GetAllQuestion}");
            ResultSet rs = csmt.executeQuery();
            while (rs.next()) {
                Model_Question quest = new Model_Question();
                quest.setId(rs.getString("ID"));
                quest.setCategory_ID(rs.getString("Category_ID"));
                quest.setContent(rs.getString("Content"));
                quest.setStatus(rs.getString("Status"));
                quest.setLast_update(rs.getString("Last_Update").toString());

                listQuestion.add(quest);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return listQuestion;
    }

    /**
     *
     * @author NUOINV get all question by ID Category
     */
    public ArrayList<Model_Question> getAllQuestionByCateID(String categoryID) {
        ArrayList<Model_Question> listQuestion = new ArrayList<Model_Question>();
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{Call sp_GetAllQuestion_ByCategoryID(?)}");
            csmt.setString("Category_ID", categoryID);
            ResultSet rs = csmt.executeQuery();
            while (rs.next()) {
                Model_Question quest = new Model_Question();
                quest.setId(rs.getString("ID"));
                quest.setCategory_ID(rs.getString("Category_ID"));
                quest.setContent(rs.getString("Content"));
                quest.setStatus(rs.getString("Status"));
                quest.setLast_update(rs.getString("Last_Update").toString());

                listQuestion.add(quest);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Question.class.getName()).log(Level.SEVERE, null, ex);
        }

        return listQuestion;
    }

    public Model_Question get_OneQuestionbyID_Status(int ID) {
        Model_Question oneQuestion = new Model_Question();

        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_GetOneQuestionbyID_Status(?)}");
            csmt.setInt("ID", ID);
            ResultSet rs = csmt.executeQuery();
            while (rs.next()) {
                oneQuestion.setId(rs.getString("ID"));
                oneQuestion.setCategory_ID("Category_ID");
                oneQuestion.setContent(rs.getString("Content"));
                oneQuestion.setStatus(rs.getString("Status"));
                oneQuestion.setLast_update(rs.getString("Last_Update").toString());
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return oneQuestion;
    }

    /**
     *
     * @author NUOINV insert  question 
     */
    public int insert_Question(Model_Question quest) {
        Integer result = 0;
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_insertQuestion(?,?,?,?)}");
            csmt.setString("Category_ID", quest.getCategory_ID());
            csmt.setString("Content", quest.getContent());
            csmt.setString("Status", quest.getStatus());
            csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);
            csmt.execute();
            result = csmt.getInt("RESULT");
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Question.class.getName()).log(Level.SEVERE, null, ex);

        }
        return result;
    }

    /**
     *
     * @author NUOINV get  question by ID 
     */
     public int Update_Question(Model_Question quest) {
        Integer result = 0;
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_UpdateQuestion(?,?,?,?,?)}");
            csmt.setString("ID", quest.getId());
            csmt.setString("Category_ID", quest.getCategory_ID());
            csmt.setString("Content", quest.getContent());
            csmt.setString("Status", quest.getStatus());
            csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);
            csmt.execute();
            result = csmt.getInt("RESULT");
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Question.class.getName()).log(Level.SEVERE, null, ex);

        }
        return result;
    }
    
    public Integer Delete_Question(String id) {
        Integer result = 0;
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{Call sp_deleteQuestionByID(?,?)}");
            csmt.setString("ID", id);
            csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);
            csmt.execute();
            result = csmt.getInt("RESULT");
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Category_Question.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public String get_TotalQuestion() {
        String total = "";
        try {
            PreparedStatement pst = DataUtil.getconnect().prepareStatement("SELECT Count(*) as Total_Quest FROM Question where Status = 0");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                total = rs.getString("Total_Quest");
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public boolean updateQuestStatusbyID(int ID, int Status) {
        try {
            CallableStatement csmt = DataUtil.getconnect().prepareCall("{call sp_updateQuestionStatusbyID(?,?)}");
            csmt.setInt("ID", ID);
            csmt.setInt("Status", Status);
            csmt.execute();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Question.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
}
